﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

using System.IO;
namespace DSLOrder
{
    /// <summary>
    /// ExcelDao 的摘要说明
    /// </summary>
    public class ExcelDao
    {
        public ExcelDao()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 查询 Excel 中的数据并存入数据表中
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        /// <param name="sql">sql命令</param>
        /// <returns>返回一个数据表</returns>
        public DataTable FindAll(OleDbConnection ocon, string sql)
        {
            OleDbCommand ocmd = new OleDbCommand(sql, ocon);
            OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "aa");
            DataTable dt = ds.Tables["aa"];
            return dt;
        }
        /// <summary>
        /// 将数据插入 Excel 中
        /// </summary>
        /// <param name="sql">sql命令</param>
        public void Insert(OleDbConnection coon, string sql, DataRow item, int i)
        {
            OleDbParameter[] pms = new OleDbParameter[3];
            OleDbCommand ocmd = new OleDbCommand(sql, coon);
            string ygbh = item["员工编号"].ToString();
            string xingming = item["xingming"].ToString();
            string DepartmentName = item["DepartmentName"].ToString();
            string jbgz = item["jbgz"].ToString();
            string gwbt = item["gwbt"].ToString();
            string glgz = item["glgz"].ToString();
            string cqgz = item["cqgz"].ToString();
            string jxgz = item["jxgz"].ToString();
            string jiabgz = item["jiabgz"].ToString();
            string dhf = item["dhf"].ToString();
            string kqkh = item["kqkh"].ToString();
            string wgwj = item["wgwj"].ToString();
            string bxf = item["bxf"].ToString();
            string pxf = item["pxf"].ToString();
            string gjj = item["gjj"].ToString();
            string jtbt = item["jtbt"].ToString();
            string jxf = item["jxf"].ToString();
            string gwgz = item["gwgz"].ToString();

            pms[0] = new OleDbParameter("@p1", ygbh);
            pms[1] = new OleDbParameter("@p2", xingming);
            pms[2] = new OleDbParameter("@p3", DepartmentName);
            pms[3] = new OleDbParameter("@p4", jbgz);
            pms[4] = new OleDbParameter("@p5", gwbt);
            pms[5] = new OleDbParameter("@p5", glgz);
            pms[6] = new OleDbParameter("@p7", cqgz);
            pms[7] = new OleDbParameter("@p8", jxgz);
            pms[8] = new OleDbParameter("@p9", jiabgz);
            pms[9] = new OleDbParameter("@p10", dhf);
            pms[10] = new OleDbParameter("@p11", kqkh);
            pms[11] = new OleDbParameter("@p12", wgwj);
            pms[12] = new OleDbParameter("@p13", bxf);
            pms[13] = new OleDbParameter("@p14", pxf);
            pms[14] = new OleDbParameter("@p15", gjj);
            pms[15] = new OleDbParameter("@p16", jtbt);
            pms[16] = new OleDbParameter("@p17", jxf);
            pms[17] = new OleDbParameter("@p18", gwgz);

            foreach (OleDbParameter iteme in pms)
            {
                ocmd.Parameters.Add(iteme);
            }
            i += ocmd.ExecuteNonQuery();
        }

        public DataSet ExcelToDS(string Path)
        {
            HttpContext.Current.Response.Write(Path);
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }
    }
}